Since its debut in 2017 with PostgreSQL 10, logical replication has fundamentally transformed how database administrators manage data distribution. It provided a native, performant, and reliable way to stream data between clusters, enabling zero-downtime migrations, cross-region replication, and complex data sharding architectures. However, for the better part of a decade, this powerful tool carried a significant "hidden" tax: the inability to replicate sequence states.
For years, database engineers have treated sequence replication as a manual, high-stakes chore. Failing to synchronize sequences—those hidden engines behind SERIAL and GENERATED AS IDENTITY columns—during a migration often resulted in immediate, catastrophic "duplicate key" violations upon cutover. With the upcoming release of PostgreSQL 19, the community has finally reached a definitive, native solution to this architectural blind spot.
The Core Problem: Why Sequences Were the "Awkward Holdout"
To understand why sequences remained elusive for so long, one must first understand their underlying nature. In PostgreSQL, sequences are non-transactional objects. When a transaction requests a value from a sequence, that value is consumed immediately and permanently, even if the transaction that requested it is later rolled back.
Logical replication, by contrast, is built entirely on the foundation of the Write-Ahead Log (WAL) and the principles of transactional integrity. It decodes the WAL, replaying operations—INSERT, UPDATE, DELETE—in the exact order they were committed on the publisher. Because sequences exist outside the standard transactional "flow" of data, they could not be easily bundled into the logical decoding stream without violating the core architectural guarantees of PostgreSQL.
For years, users relied on stopgap measures. The most common practice involved executing a setval() script on the subscriber during the final cutover window. This required a carefully calculated "safety buffer"—typically adding 1,000 to the last_value—to account for any writes that might occur on the publisher during the final seconds of the migration. It was a brittle, error-prone process that required perfect timing and deep familiarity with the database’s internal state.
Chronology of the Struggle
The path to native sequence replication was paved with both ambitious attempts and sobering failures.
The Era of Extensions (Pre-PostgreSQL 10)
Before logical replication existed, the pglogical extension emerged as the gold standard for publish/subscribe data movement. Crucially, pglogical recognized the necessity of sequence tracking early on. It allowed administrators to enroll sequences into replication sets and used background workers to periodically update subscriber sequences. While effective, it was never a real-time solution; it relied on the same "safety buffer" logic that manual scripts used, acknowledging that true real-time sequence streaming was architecturally incompatible with the core design.
The Patch that Almost Was (PostgreSQL 16)
The community’s frustration culminated in a major push during the development cycle of PostgreSQL 16. Tomas Vondra, a prominent PostgreSQL contributor, spent years crafting a sophisticated patch designed to include sequence advancements directly within the logical decoding stream. The patch was successfully committed to the development branch, sparking widespread celebration. However, as the release cycle neared its end, the patch was abruptly reverted.
The reversal was a stark reminder of the complexity of the task: the team discovered that reconciling the transactional nature of the WAL with the non-transactional nature of sequences created insurmountable reliability risks. Attempting to force them together introduced performance overhead and, more dangerously, raised concerns about order-of-operation conflicts in highly concurrent environments.
The Breakthrough: Embracing the "Sync" Philosophy
The solution that finally arrives in PostgreSQL 19 is elegant in its simplicity. Instead of attempting the "impossible" task of continuous, real-time streaming of non-transactional sequence data, the development team—led by Vignesh C and Tomas Vondra—opted to treat sequence synchronization as a discrete, intentional operation.
Rather than trying to force the sequence into the transactional WAL-replay loop, PostgreSQL 19 introduces a mechanism to synchronize the sequence state at defined, "safe" moments. By moving the goalpost from "constant streaming" to "point-in-time synchronization," the team bypassed the architectural conflicts that had stalled progress for years.
How to Implement: The New Syntax
PostgreSQL 19 introduces the ALL SEQUENCES clause, which significantly streamlines the setup process for publications.
Publication and Subscription
Administrators can now include sequences in a publication with a single command:
CREATE PUBLICATION migration_pub FOR ALL TABLES, ALL SEQUENCES;This command ensures that not only is existing data replicated, but all sequences associated with the tables are tracked. When a subscription is created on the subscriber side, the initial values of these sequences are pulled across as part of the bootstrap process.
The Cutover Strategy
The real power lies in the new ALTER SUBSCRIPTION command. When preparing for a cutover, the administrator no longer needs to run external setval() scripts. Instead, they can trigger a native sync:
ALTER SUBSCRIPTION migration_sub REFRESH SEQUENCES;This command reconciles the subscriber’s sequence values with the publisher’s current state. For scenarios where the schema has evolved, REFRESH PUBLICATION can be used to perform both a full schema reconciliation and a sequence value update in one pass.
Supporting Data and Monitoring
Visibility into the state of these sequences is handled through new diagnostic tools. The function pg_get_sequence_data() allows for direct verification of the last_value on both sides of the replication bridge. Furthermore, the pg_stat_subscription_stats view has been updated to include a sync_seq_error_count column. This allows DBAs to monitor for failures in the synchronization process, ensuring that the critical "final step" of the migration has completed successfully before traffic is redirected.
Implications for Database Architecture
The implications of this update are profound for both the PostgreSQL ecosystem and the individual DBA.
1. Simplification of Migration Pipelines
The most immediate impact is the removal of a significant "gotcha" in database migrations. By reducing the sequence sync to a single, native command, PostgreSQL eliminates the risk of human error in manual scripts, effectively shrinking the window for potential downtime during cutovers.
2. A Lesson in Architectural Purity
The decade-long struggle to implement sequence replication serves as a masterclass in software engineering restraint. By refusing to merge a "good enough" patch that might have compromised transactional integrity, the PostgreSQL community maintained the database’s reputation for rock-solid reliability. The final solution respects the design principles of the system rather than attempting to force a square peg into a round hole.
3. Future-Proofing for Modern Apps
While this feature is a massive win for standard migrations, the community remains clear about its scope. It is not designed for high-concurrency, multi-master write scenarios where sequences might collide globally. For those advanced use cases, the recommendation remains the use of UUIDs or specialized sequence generators like "Snowflake" IDs. PostgreSQL 19 solves the "migration" problem; it does not turn PostgreSQL into a distributed global sequence generator.
Conclusion: A Milestone for the Community
PostgreSQL 19’s inclusion of native sequence synchronization is a testament to the patient, persistent nature of open-source development. It transforms a task that was once a source of anxiety—the midnight cutover—into a standardized, verifiable operation.
For database professionals, the message is clear: the era of manual setval() scripts and high-stakes "buffer math" is coming to an end. As we approach the official release of version 19, now is the time to begin testing these features in staging environments. The long-forgotten sequence has finally found its place in the logical replication stream, closing one of the final chapters of manual configuration that has long defined the PostgreSQL migration experience.
